<?php if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class Work_order_model extends CI_Model {
	public function __construct() {
		parent::__construct();
	}
	public function get_maintain_type() {
		$sql = "SELECT * FROM app_lookup_values WHERE lookup_type = 'UBS_MAINTAIN_TYPE'";
		$query = $this->db->query($sql);
		return $query->result();
	}

	public function updateDataDev($data) {
		$this->db->query("call ar_device_upd(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,@err,@msg)", $data);
		$que = $this->db->query("SELECT @id as id,@err as err,@msg as msg");
		$id;
		$msg;
		$err;
		foreach ($que->result() as $keys) {
			$msg = $keys->msg;
			$err = $keys->err;
			$id = $keys->id;
		}
		$arr = array(
			'msg' => $msg,
			'err' => $err,
			'id' => $id,
		);
		return $arr;
	}

	public function get_account_num($id){
		$sql = "SELECT acct_num FROM ar_account WHERE account_id = '".$id."' ";
		$query = $this->db->query($sql);
		$acct;
		foreach ($query->result() as $keys) {
			$acct = $keys->acct_num;
		}
		$arr = array('acct_num' => $acct);
		return $arr;
	}


	public function get_work_order_data($id) {
		$sql = "SELECT uwo.maintain_id maintain_id
                        , uwo.account_id account_id
                        , uwo.maintain_code maintain_code
                        , alv.meaning meaning
                        , aa.acct_name name
                        , au.user_name assigned
                        , au2.user_name completed
                        , uwo.status status
                        , uwo.target_date target_date
                        , uwo.actual_completion_date completion_date
                        , uwo.last_meter_reading last_reading
                        , uwo.new_meter_reading new_reading
                  FROM ubs_work_order uwo
                    INNER JOIN app_lookup_values alv ON alv.lookup_code = uwo.maintain_code AND alv.lookup_type = 'UBS_MAINTAIN_TYPE'
                    INNER JOIN ar_account aa ON aa.account_id = uwo.account_id
                    INNER JOIN app_users au ON au.user_id = uwo.assigned_to
                    LEFT JOIN app_users au2 ON au2.user_id = uwo.completed_by
                  WHERE uwo.maintain_id = '" . $id . "' ";
		$query = $this->db->query($sql);
		return $query->result();
	}

	public function get_last_read($id) {
		$sql = "SELECT uwo.last_meter_reading last_reading FROM ubs_work_order uwo INNER JOIN(SELECT MAX(update_date) max FROM ubs_work_order WHERE maintain_code = 'DIS') m ON m.max = uwo.update_date WHERE uwo.account_id = '" . $id . "' ";
		$query = $this->db->query($sql);
		return $query->result();
	}

	public function get_operators() {
		$sql = "SELECT au.user_id user_id , au.user_name user_name FROM app_users au
                  INNER JOIN app_user_roles aur ON aur.user_id = au.user_id
              WHERE aur.role_id ='19' ";
		$query = $this->db->query($sql);
		return $query->result();
	}

	public function check_role($id) {
		$sql = "SELECT * FROM `app_user_roles` WHERE user_id = '" . $id . "'  AND role_id = '20' ";
		$query = $this->db->query($sql);
		if ($query->num_rows() != 0) {
			return true;
		} else {
			return false;
		}
	}

	public function update_work_order($data) {
		$this->db->query("call ubs_work_order_upd(?,?,?,?,@err,@msg)", $data);
		$que = $this->db->query("SELECT @err as error , @msg as msg");
		echo json_encode($que->result());
	}
	public function update_work_order_comp_new($data) {
		$this->db->query("call ubs_work_order_comp_new(?,?,?,?,@err,@msg,?)", $data);
		$que = $this->db->query("SELECT @err as error , @msg as msg");
		echo json_encode($que->result());
	}
	public function update_work_order_comp_rep($data) {
		$this->db->query("call ubs_work_order_comp_rep(?,?,?,?,?,@err,@msg)", $data);
		$que = $this->db->query("SELECT @err as error , @msg as msg");
		echo json_encode($que->result());
	}
	public function update_work_order_comp_dis($data) {
		$this->db->query("call ubs_work_order_comp_dis(?,?,?,?,@err,@msg)", $data);
		$que = $this->db->query("SELECT @err as error , @msg as msg");
		echo json_encode($que->result());
	}

	public function get_wo_data($id) {
		$sql = "SELECT * FROM `ubs_work_order` uwo INNER JOIN ar_account aa ON aa.account_id = uwo.account_id  WHERE uwo.maintain_id = '" . $id . "'";
		$query = $this->db->query($sql);
		return $query->result();
	}
	public function get_role_id($id) {
		$sql = "SELECT * FROM `app_user_roles` WHERE user_id = '" . $id . "'";
		$query = $this->db->query($sql);
		return $query->result();
	}

	public function insert_work_order($data) {
		$this->db->query("call ubs_work_order_ins(?,?,?,?,?,?,@id,@err,@msg)", $data);
		$que = $this->db->query("SELECT @id as id, @err as error , @msg as msg");
		echo json_encode($que->result());
	}
	public function insert_work_order_rec($data) {
		$this->db->query("call ubs_work_order_ins_rec(?,?,?,?,?,?,?,@id,@err,@msg)", $data);
		$que = $this->db->query("SELECT @id as id, @err as error , @msg as msg");
		echo json_encode($que->result());
	}

	public function get_pending_cus() {
		$sql = "SELECT * FROM ubs_get_pending_accts_v";
		$query = $this->db->query($sql);
		return $query->result();
	}
	public function get_active_cus() {
		$sql = "SELECT * FROM ubs_get_active_accts_v";
		$query = $this->db->query($sql);
		return $query->result();
	}
	public function get_inactive_cus() {
		$sql = "SELECT * FROM ubs_get_inactive_accts ";
		$query = $this->db->query($sql);
		return $query->result();
	}
	public function get_mother_meter() {
		$sql = "SELECT * FROM ubs_mother_meter ";
		$query = $this->db->query($sql);
		return $query->result();
	}

	public function countArr($arr, $query) {
		$counter = 0;
		foreach ($arr as $key) {
			$counter++;
		}
		$arr[$counter] = $query;
		return $arr;
	}
	public function search_work_order($wo_type, $wo_acct_name, $wo_status, $wo_assign,$wo_tardate,$wo_comdate) {
		$sql;
		$arg1 = "";
		$arg2 = "";
		$arg3 = "";
		$arg4 = "";
		$arg5 = "";
		$arr = array();

		$setter = 0;

		if ($wo_type != "") {
			$arg1 = "alv.meaning LIKE '" . $wo_type . "'";
			$setter = 1;
			$arr = $this->countArr($arr, $arg1);
		}
		if ($wo_acct_name != "") {
			$arg2 = "aa.acct_name LIKE '" . $wo_acct_name . "'";
			$setter = 1;
			$arr = $this->countArr($arr, $arg2);
		}
		if ($wo_status != "") {
			$arg3 = "uwo.status LIKE '" . $wo_status . "'";
			$setter = 1;
			$arr = $this->countArr($arr, $arg3);
		}
		if ($wo_assign != ""){
			$arg4 = "au.user_id = '" . $wo_assign . "'";
			$setter = 1;
			$arr = $this->countArr($arr, $arg4);
		}
		if ($wo_tardate != ""){
			$arg5 = "uwo.target_date = '" . $wo_tardate . "'";
			$setter = 1;
			$arr = $this->countArr($arr, $arg5);
		}
		if ($wo_comdate != ""){
			$arg6 = "uwo.actual_completion_date = '" . $wo_comdate . "'";
			$setter = 1;
			$arr = $this->countArr($arr, $arg6);
		}
		if ($setter == 1) {
			$que = implode(" AND ", $arr);
			$sql = "SELECT uwo.maintain_id maintain_id
                        , uwo.maintain_code maintain_code
                        , alv.meaning meaning
                        , aa.acct_name name
                        , au.user_name assigned
                        , au.user_id id
                        , au2.user_name completed
                        , uwo.status status
                        , uwo.target_date target_date
                        , uwo.actual_completion_date completion_date
                        , uwo.last_meter_reading last_reading
                        , uwo.new_meter_reading new_reading
                  FROM ubs_work_order uwo
                    INNER JOIN app_lookup_values alv ON alv.lookup_code = uwo.maintain_code AND alv.lookup_type = 'UBS_MAINTAIN_TYPE'
                    INNER JOIN ar_account aa ON aa.account_id = uwo.account_id
                    INNER JOIN app_users au ON au.user_id = uwo.assigned_to
                    LEFT JOIN app_users au2 ON au2.user_id = uwo.completed_by
                  WHERE " . $que . " ";
		} else {
			$sql = "SELECT * FROM ubs_work_order WHERE maintain_id = 'adas1mojan9mad212e2)(#*7' ";
		}
		$query = $this->db->query($sql);
		if ($query->num_rows() != 0) {
			return $query->result();
		} else {
			$result = "No results found.";
			return $result;
		}
	}

	public function get_dev_type() {
		$query = $this->db->get('ar_device_type_v');
		return $query->result();
	}
	public function getcycleunit() {
		$this->db->where('lookup_type', "CYCLE_UNIT");
		$q = $this->db->get('app_lookup_values');
		return $q->result();
	}
	public function get_dev_stats() {
		$query = $this->db->get('ar_device_status_v');
		return $query->result();
	}
	public function get_item() {
		$query = $this->db->get('inv_items');
		return $query->result();
	}
	public function insertDataDev($data) {
		$this->db->query("call ar_device_ins(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,@id,@err,@msg,?)", $data);
		$que = $this->db->query("SELECT @id as id,@err as err,@msg as msg");
		$id;
		$msg;
		$err;
		foreach ($que->result() as $keys) {
			$msg = $keys->msg;
			$err = $keys->err;
			$id = $keys->id;
		}
		$arr = array(
			'msg' => $msg,
			'err' => $err,
			'id' => $id,
		);
		return $arr;
	}

	public function get_dev3($id,$type) {
		$this->db->where('account_id', $id);
		if($type == "REP" || $type == "NEW"){
			$this->db->where('status', "PND");
		}else{
			$this->db->where('status', "ACTV");
		}
		$query = $this->db->get('ar_device', 1);
		return $query->result();
	}
	public function get_dev($id) {
		$this->db->where('account_id', $id);
		$query = $this->db->get('ar_device', 1);
		return $query->result();
	}
	public function get_dev2($id,$type) {
		$this->db->where('account_id', $id);
		if($type == "REP" || $type == "NEW"){
			$this->db->where('status', "PND");
		}else{
			$this->db->where('status', "ACTV");
		}
		$query = $this->db->get('ar_device', 1);
		return $query->result();
	}
}